package it.unisa.unibox.Storage;

import it.unisa.unibox.beans.Account;
import it.unisa.unibox.beans.AreaScientifica;
import it.unisa.unibox.beans.Dipartimento;
import it.unisa.unibox.beans.Notifica;
import it.unisa.unibox.beans.Prodotto;
import it.unisa.unibox.beans.Ricercatore;
import it.unisa.unibox.bridge.IUtilityQuerysManager;
import it.unisa.unibox.commons.DBConnection;
import it.unisa.unibox.commons.DBNames;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class DBUtilityQuerys implements IUtilityQuerysManager {

	private static DBUtilityQuerys dbutility;

	/**
	 * Singleton pattern, restituisce un'istanza dell'oggetto
	 * @return l'oggetto
	 */
	public static DBUtilityQuerys getInstance() {
		if (dbutility == null)
			dbutility = new DBUtilityQuerys();
		return dbutility;
	}

	@Override
	public ArrayList<Dipartimento> getDipartimenti() throws SQLException {
		
		String query = "SELECT * FROM dipartimento;";
		ResultSet rs = DBConnection.execQuery(query);
		ArrayList<Dipartimento> list = new ArrayList<Dipartimento>();
		int id;
		String nome;
		while (rs.next()) {
			id = rs.getInt("ID");
			nome = rs.getString("nome");
			Dipartimento d = new Dipartimento(id, nome);
			list.add(d);
		}
		
		return list;
	}

	@Override
	public ArrayList<AreaScientifica> getAreeScientifiche() throws SQLException {
		
		String query = "SELECT * FROM area_scientifica;";
		ResultSet rs = DBConnection.execQuery(query);
		ArrayList<AreaScientifica> list = new ArrayList<AreaScientifica>();
		int id;
		String nome;
		int codice;
		while (rs.next()) {
			id = rs.getInt("ID");
			nome = rs.getString("nome");
			codice = rs.getInt("codice");
			AreaScientifica as = new AreaScientifica(id, nome, codice);
			list.add(as);
		}
		
		return list;
	}

	public ArrayList<Notifica> getNotifiche(int idRicercatore)
			throws SQLException {
		
		String query = "SELECT * FROM `notifica` WHERE `destinatario`='"
				+ idRicercatore + "';";
		ResultSet rs = DBConnection.execQuery(query);
		ArrayList<Notifica> list = new ArrayList<Notifica>();
		while (rs.next()) {
			int id = rs.getInt("ID");
			Ricercatore mittente = DBUtilityQuerys.getInstance()
					.retrieveRicercatore(rs.getInt("mittente"));
			Ricercatore destinatario = DBUtilityQuerys.getInstance()
					.retrieveRicercatore(rs.getInt("destinatario"));
			int idProdotto = rs.getInt("ID_Prodotto");
			Prodotto p = DBGestioneProdotti.getInstance().getProdotto(
					idProdotto);
			int stato = rs.getInt("stato");
			String data = rs.getString("data");
			Notifica n = new Notifica(id, mittente, destinatario, p, stato,
					data);
			list.add(n);
		}
		return list;
	}

	public Ricercatore retrieveRicercatore(int idRicercatore)
			throws SQLException {
		
		String query = "SELECT * FROM ricercatore WHERE ID='" + idRicercatore+"';";
		ResultSet rs = DBConnection.execQuery(query);
		Ricercatore ricercatore = null;
		int id;
		String nome;
		String cognome;
		String codFisc;
		String sesso;
		String email;
		String cittaNascita;
		Account account = null;
		Dipartimento dipartimento;
		AreaScientifica areaScientifica;
		int matricola;
		int ruolo;
		String dataNascita;
		String dataInizioServizio;
		if (rs.next()) {
			id = rs.getInt("ID");
			nome = rs.getString("nome");
			cognome = rs.getString("cognome");
			codFisc = rs.getString("codFisc");
			sesso = rs.getString("sesso");
			email = rs.getString("email");
			cittaNascita = rs.getString("cittaNascita");

			dipartimento = DBUtilityQuerys.getInstance().retrieveDipartimento(
					rs.getInt("dipartimento"));
			areaScientifica = DBUtilityQuerys.getInstance()
					.retrieveAreaScientifica(rs.getInt("area_scientifica"));
			matricola = rs.getInt("matricola");
			ruolo = rs.getInt("ruolo");
			dataNascita = rs.getString("dataNascita");
			dataInizioServizio = rs.getString("dataInizioServizio");
			ricercatore = new Ricercatore(id, account, nome, cognome, codFisc,
					sesso, email, cittaNascita, dipartimento, areaScientifica,
					matricola, ruolo, dataNascita, dataInizioServizio);
		}
		
		return ricercatore;
	}

	public Dipartimento retrieveDipartimento(int idDipartimento)
			throws SQLException {
		
		String query = "SELECT * FROM  `dipartimento` WHERE ID ="
				+ idDipartimento;
		ResultSet rs = DBConnection.execQuery(query);
		Dipartimento dip = null;
		int id;
		String nome;
		if (rs.next()) {
			id = rs.getInt("ID");
			nome = rs.getString("nome");
			dip = new Dipartimento(id, nome);
		}
		return dip;
	}

	public AreaScientifica retrieveAreaScientifica(int idAreaScientifica)
			throws SQLException {
		
		String query = "SELECT * FROM  `area_scientifica` WHERE ID ="
				+ idAreaScientifica;
		ResultSet rs = DBConnection.execQuery(query);
		AreaScientifica ae = null;
		int id;
		String nome;
		int codice;
		if (rs.next()) {
			id = rs.getInt("ID");
			nome = rs.getString("nome");
			codice = rs.getInt("codice");
			ae = new AreaScientifica(id, nome, codice);
		}
		return ae;
	}

	/**
	 * query di supporto per RF 0.1, restituisce tutti i prodotti di cui il
	 * ricercatore idRicercatore è responsabile o coautore
	 */
	@Override
	public ArrayList<Prodotto> getProdottiHome(int idRicercatore)
			throws SQLException {

		

		ArrayList<Prodotto> list = new ArrayList<Prodotto>();

		// Seleziono i prodotti di cui sono responsabile

		String query = "SELECT * FROM ricercatore "
				+ "INNER JOIN prodotto ON ricercatore.ID = prodotto.responsabile "
				+ "INNER JOIN ACCOUNT ON ricercatore.id_account = account.id "
				+ "INNER JOIN dipartimento ON ricercatore.dipartimento = dipartimento.ID "
				+ "INNER JOIN area_scientifica ON ricercatore.area_scientifica = area_scientifica.ID "
				+ "WHERE responsabile=" + idRicercatore;

		ResultSet rs = DBConnection.execQuery(query);

		while (rs.next()) {
			list.add(new Prodotto(rs.getInt(DBNames.TABLE_PRODOTTO + "."
					+ DBNames.ATT_PRODOTTO_ID), new Ricercatore(rs
					.getInt(DBNames.TABLE_RICERCATORE + "."
							+ DBNames.ATT_RICERCATORE_ID), new Account(rs
					.getInt(DBNames.TABLE_ACCOUNT + "."
							+ DBNames.ATT_ACCOUNT_ID), rs
					.getString(DBNames.TABLE_ACCOUNT + "."
							+ DBNames.ATT_ACCOUNT_USERNAME), rs
					.getString(DBNames.TABLE_ACCOUNT + "."
							+ DBNames.ATT_ACCOUNT_PASSWORD), rs
					.getInt(DBNames.TABLE_ACCOUNT + "."
							+ DBNames.ATT_ACCOUNT_TIPOLOGIA)), rs
					.getString(DBNames.TABLE_RICERCATORE + "."
							+ DBNames.ATT_RICERCATORE_NOME), rs
					.getString(DBNames.TABLE_RICERCATORE + "."
							+ DBNames.ATT_RICERCATORE_COGNOME), rs
					.getString(DBNames.TABLE_RICERCATORE + "."
							+ DBNames.ATT_RICERCATORE_CODICE_FISCALE), rs
					.getString(DBNames.TABLE_RICERCATORE + "."
							+ DBNames.ATT_RICERCATORE_SESSO), rs
					.getString(DBNames.TABLE_RICERCATORE + "."
							+ DBNames.ATT_RICERCATORE_EMAIL), rs
					.getString(DBNames.TABLE_RICERCATORE + "."
							+ DBNames.ATT_RICERCATORE_CITTA_NASCITA),
					new Dipartimento(rs.getInt(DBNames.TABLE_DIPARTIMENTO + "."
							+ DBNames.ATT_DIPARTIMENTO_ID), rs
							.getString(DBNames.TABLE_DIPARTIMENTO + "."
									+ DBNames.ATT_DIPARTIMENTO_NOME)),
					new AreaScientifica(rs
							.getInt(DBNames.TABLE_AREA_SCIENTIFICA + "."
									+ DBNames.ATT_AREA_SCIENTIFICA_ID), rs
							.getString(DBNames.TABLE_AREA_SCIENTIFICA + "."
									+ DBNames.ATT_AREA_SCIENTIFICA_NOME), rs
							.getInt(DBNames.TABLE_AREA_SCIENTIFICA + "."
									+ DBNames.ATT_AREA_SCIENTIFICA_CODICE)), rs
							.getInt(DBNames.TABLE_RICERCATORE + "."
									+ DBNames.ATT_RICERCATORE_MATRICOLA), rs
							.getInt(DBNames.TABLE_RICERCATORE + "."
									+ DBNames.ATT_RICERCATORE_RUOLO), rs
							.getString(DBNames.TABLE_RICERCATORE + "."
									+ DBNames.ATT_RICERCATORE_DATA_NASCITA),
					rs.getString(DBNames.TABLE_RICERCATORE + "."
							+ DBNames.ATT_RICERCATORE_DATA_INIZIO_SERVIZIO)),
					rs.getString(DBNames.TABLE_PRODOTTO + "."
							+ DBNames.ATT_PRODOTTO_TIPOLOGIA), rs
							.getString(DBNames.TABLE_PRODOTTO + "."
									+ DBNames.ATT_PRODOTTO_TITOLO), rs
							.getString(DBNames.TABLE_PRODOTTO + "."
									+ DBNames.ATT_PRODOTTO_ABSTRACT), rs
							.getInt(DBNames.TABLE_PRODOTTO + "."
									+ DBNames.ATT_PRODOTTO_STATO), rs
							.getInt(DBNames.TABLE_PRODOTTO + "."
									+ DBNames.ATT_PRODOTTO_PUBBLICO), rs
							.getInt(DBNames.TABLE_PRODOTTO + "."
									+ DBNames.ATT_PRODOTTO_ANNO), rs
							.getString(DBNames.TABLE_PRODOTTO + "."
									+ DBNames.ATT_PRODOTTO_LINGUA), rs
							.getString(DBNames.TABLE_PRODOTTO + "."
									+ DBNames.ATT_PRODOTTO_URL), rs
							.getString(DBNames.TABLE_PRODOTTO + "."
									+ DBNames.ATT_PRODOTTO_NOTE)));
		}

		// Seleziono i prodotti di cui sono coautore

		query = "SELECT * FROM ricercatore "
				+ "INNER JOIN account ON ricercatore.id_account = account.id "
				+ "INNER JOIN lista_autori ON ricercatore.ID = lista_autori.ID_Ricercatore "
				+ "INNER JOIN prodotto ON lista_autori.ID_Prodotto = prodotto.ID "
				+ "INNER JOIN dipartimento ON ricercatore.dipartimento = dipartimento.ID "
				+ "INNER JOIN area_scientifica ON ricercatore.area_scientifica = area_scientifica.ID "
				+ "WHERE lista_autori.ID_Ricercatore=" + idRicercatore;

		rs = DBConnection.execQuery(query);
		while (rs.next()) {
			list.add(new Prodotto(rs.getInt(DBNames.TABLE_PRODOTTO + "."
					+ DBNames.ATT_PRODOTTO_ID),
					DBUtilityQuerys.getInstance().retrieveRicercatore(
					rs.getInt("prodotto.responsabile")), rs
					.getString(DBNames.TABLE_PRODOTTO + "."
							+ DBNames.ATT_PRODOTTO_TIPOLOGIA), rs
					.getString(DBNames.TABLE_PRODOTTO + "."
							+ DBNames.ATT_PRODOTTO_TITOLO), rs
					.getString(DBNames.TABLE_PRODOTTO + "."
							+ DBNames.ATT_PRODOTTO_ABSTRACT), rs
					.getInt(DBNames.TABLE_PRODOTTO + "."
							+ DBNames.ATT_PRODOTTO_STATO), rs
					.getInt(DBNames.TABLE_PRODOTTO + "."
							+ DBNames.ATT_PRODOTTO_PUBBLICO), rs
					.getInt(DBNames.TABLE_PRODOTTO + "."
							+ DBNames.ATT_PRODOTTO_ANNO), rs
					.getString(DBNames.TABLE_PRODOTTO + "."
							+ DBNames.ATT_PRODOTTO_LINGUA), rs
					.getString(DBNames.TABLE_PRODOTTO + "."
							+ DBNames.ATT_PRODOTTO_URL), rs
					.getString(DBNames.TABLE_PRODOTTO + "."
							+ DBNames.ATT_PRODOTTO_NOTE)));
		}

		
		return list;

	}

	/**
	 * Metodo per l'autocompletaemento del campo autori
	 * 
	 * @param field
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<Ricercatore> autocompletamentoAutori() throws SQLException {
		
		String query = "SELECT * FROM ricercatore "
				+ "INNER JOIN account ON ricercatore.id_account = account.id "
				+ "INNER JOIN dipartimento ON ricercatore.dipartimento = dipartimento.ID "
				+ "INNER JOIN area_scientifica ON ricercatore.area_scientifica = area_scientifica.ID";
		//System.out.println(query);
		ResultSet rs = DBConnection.execQuery(query);
		ArrayList<Ricercatore> list = new ArrayList<Ricercatore>();
		while (rs.next()) {
			list.add(new Ricercatore(rs.getInt(DBNames.TABLE_RICERCATORE + "."
					+ DBNames.ATT_RICERCATORE_ID), new Account(rs
					.getInt(DBNames.TABLE_ACCOUNT + "."
							+ DBNames.ATT_ACCOUNT_ID), rs
					.getString(DBNames.TABLE_ACCOUNT + "."
							+ DBNames.ATT_ACCOUNT_USERNAME), rs
					.getString(DBNames.TABLE_ACCOUNT + "."
							+ DBNames.ATT_ACCOUNT_PASSWORD), rs
					.getInt(DBNames.TABLE_ACCOUNT + "."
							+ DBNames.ATT_ACCOUNT_TIPOLOGIA)), rs
					.getString(DBNames.TABLE_RICERCATORE + "."
							+ DBNames.ATT_RICERCATORE_NOME), rs
					.getString(DBNames.TABLE_RICERCATORE + "."
							+ DBNames.ATT_RICERCATORE_COGNOME), rs
					.getString(DBNames.TABLE_RICERCATORE + "."
							+ DBNames.ATT_RICERCATORE_CODICE_FISCALE), rs
					.getString(DBNames.TABLE_RICERCATORE + "."
							+ DBNames.ATT_RICERCATORE_SESSO), rs
					.getString(DBNames.TABLE_RICERCATORE + "."
							+ DBNames.ATT_RICERCATORE_EMAIL), rs
					.getString(DBNames.TABLE_RICERCATORE + "."
							+ DBNames.ATT_RICERCATORE_CITTA_NASCITA),
					new Dipartimento(rs.getInt(DBNames.TABLE_DIPARTIMENTO + "."
							+ DBNames.ATT_DIPARTIMENTO_ID), rs
							.getString(DBNames.TABLE_DIPARTIMENTO + "."
									+ DBNames.ATT_DIPARTIMENTO_NOME)),
					new AreaScientifica(rs
							.getInt(DBNames.TABLE_AREA_SCIENTIFICA + "."
									+ DBNames.ATT_AREA_SCIENTIFICA_ID), rs
							.getString(DBNames.TABLE_AREA_SCIENTIFICA + "."
									+ DBNames.ATT_AREA_SCIENTIFICA_NOME), rs
							.getInt(DBNames.TABLE_AREA_SCIENTIFICA + "."
									+ DBNames.ATT_AREA_SCIENTIFICA_CODICE)), rs
							.getInt(DBNames.TABLE_RICERCATORE + "."
									+ DBNames.ATT_RICERCATORE_MATRICOLA), rs
							.getInt(DBNames.TABLE_RICERCATORE + "."
									+ DBNames.ATT_RICERCATORE_RUOLO), rs
							.getString(DBNames.TABLE_RICERCATORE + "."
									+ DBNames.ATT_RICERCATORE_DATA_NASCITA),
					rs.getString(DBNames.TABLE_RICERCATORE + "."
							+ DBNames.ATT_RICERCATORE_DATA_INIZIO_SERVIZIO)));
		}
		
		return list;
	}

	/**
	 * Inserisce il prodotto nella tabella prodotti del database lasciando la
	 * generazione dell'id al DBMS; l'inserimento deve successivamente essere
	 * completato con l'inserimento nella tabella della tipologia opportuna
	 * 
	 * @return l'id generato dal database
	 * @throws SQLException
	 */
	public int insertProdotto(Prodotto p) throws SQLException {
		
		
		// inserisco nella tabella prodotto
		String format = "INSERT INTO `prodotto`(`responsabile`, `tipologia`, `titolo`, `abstract`, `stato`, `pubblico`, `anno`, `lingua`, `url`, `note`)"
				+ " VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');";
		String query = String.format(format, p.getResponsabile().getId(),
				p.getTipologia(), p.getTitolo(), p.getAbstract_field(),
				p.getStato(), p.getPubblico(), p.getAnno(), p.getLingua(),
				p.getUrl(), p.getNote());
		System.out.println(query);
		int ret = DBConnection.execUpdate(query);
		//System.out.println("valore di ritorno executeUpdate: " + ret);
		if (ret == 0) {
			throw new SQLException();
		}
		// recupero il prodotto appena inserito dal database per prendere l'id
		String format1 = "Select MAX(prodotto.ID) AS m FROM prodotto WHERE responsabile='%s' && tipologia='%s' && titolo='%s' && abstract='%s' && stato='%s' && pubblico='%s' && anno='%s' && lingua='%s' && url='%s' && note='%s'";
		String query1 = String.format(format1, p.getResponsabile().getId(),
				p.getTipologia(), p.getTitolo(), p.getAbstract_field(),
				p.getStato(), p.getPubblico(), p.getAnno(), p.getLingua(),
				p.getUrl(), p.getNote());
		//System.out.println("query1= " + query1);
		ResultSet rs = DBConnection.execQuery(query1);
		int rq;
		if (rs.next())
			rq = rs.getInt("m");
		else
			throw new SQLException();
		
		//System.out.println("ID generato dal DBMS: " + rq);
		return rq;
	}

	/**
	 * Aggiorna il prodotto avente 'prodotti'.ID = p.id nella tabella prodotti
	 * del database; i campi di p devono essere ben formati, ovvero ciascun
	 * campo dell'oggetto sarà inserito nella relativa entry del database; la
	 * modifica deve successivamente essere completata anche nella tabella della
	 * relativa tipologia.
	 * 
	 * @param p
	 *            un prodotto con tutti i campi ben formati; p.id DEVE essere
	 *            reperibile nella tabella prodotti del database
	 * @return 1 se l'aggioramento ha successo, 0 altrimenti
	 */
	public int updateProdotto(Prodotto p) throws SQLException {
		
		// aggiorno nella tabella prodotto
		String format = "UPDATE `prodotto` SET `responsabile`='%s',`tipologia`='%s',`titolo`='%s',`abstract`='%s',`stato`='%s',`pubblico`='%s',`anno`='%s',`lingua`='%s',`url`='%s',`note`='%s' WHERE  `ID`='%s';";
		String query = String.format(format, p.getResponsabile().getId(),
				p.getTipologia(), p.getTitolo(), p.getAbstract_field(),
				p.getStato(), p.getPubblico(), p.getAnno(), p.getLingua(),
				p.getUrl(), p.getNote(), p.getId());
		//System.out.println(query);
		int ret = DBConnection.execUpdate(query);
		//System.out.println("valore di ritorno executeUpdate in updateProdotto(): " + ret);
		if (ret == 0)
			throw new SQLException();
		else
			return ret;
	}

	public boolean aggiungiAutore(int idProdotto, int idCoautore,
			int idResponsabile) throws SQLException {
		
		// inserice l'autore con id = idCoautore
		String format = "INSERT INTO `lista_autori`(`ID_Prodotto`, `ID_Ricercatore`) VALUES ('%s','%s');";
		String query = String.format(format, idProdotto, idCoautore);
		int ret = DBConnection.execUpdate(query);
		// inserice la notifica al coautore
		format = "INSERT INTO `notifica`(`mittente`, `destinatario`, `ID_Prodotto`, `data`, `stato`) VALUES ('%s','%s','%s','%s','%s');";
		query = String.format(format, idResponsabile, idCoautore, idProdotto,
				new Date(System.currentTimeMillis()), 0);
		DBConnection.execUpdate(query);
		
		return (ret == 1);
	}
	
	public ArrayList<Ricercatore> getListaAutori(int idProdotto) throws SQLException {
		
		// inserice l'autore con id = idCoautore
		String query = "SELECT ricercatore.id from ricercatore, lista_autori WHERE lista_autori.ID_Ricercatore=ricercatore.ID AND lista_autori.ID_Prodotto="+idProdotto;
		ResultSet rs = DBConnection.execQuery(query);
		ArrayList<Ricercatore> list = new ArrayList<Ricercatore>();
		while (rs.next())
			list.add(DBUtilityQuerys.getInstance().retrieveRicercatore(rs.getInt("id")));
	
		
		return list;
		
	}

	public void rimuoviAutori(int idProdotto) throws SQLException {
		String query = " DELETE FROM `db_unibox`.`lista_autori` WHERE `lista_autori`.`ID_Prodotto` = '" + idProdotto + "';";
		DBConnection.execUpdate(query);
	}
}
